package com.byd.sealmanage.util;

import com.monitorjbl.xlsx.StreamingReader;
import com.monitorjbl.xlsx.impl.StreamingCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.ResultSet;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * @author ls
 * @date 2020/5/22
 */
public class ExcelUtil {

    /**
     * 导出excel
     * @param tbExcelConfig
     * @param results
     * @param map
     * @param request
     * @param response
     * @throws NoSuchFieldException
     * @throws IllegalAccessException
     */
//    public static void exportBoardExcel(TbExcelConfig tbExcelConfig, List<CyMadeSelectResult> results, ModelMap map, HttpServletRequest request, HttpServletResponse response) throws NoSuchFieldException, IllegalAccessException {
//        Date nowTime = new Date();
//        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
//        String  date = sdf.format(nowTime);
//        String[] label = tbExcelConfig.getConfigName().split(",");
//        String[] value = tbExcelConfig.getConfigValue().split(",");
//        CyMadeSelectResult cyMadeSelectResult = new CyMadeSelectResult();
//        for (int i = 0; i < label.length; i++) {
//            Field field = cyMadeSelectResult.getClass().getDeclaredField(value[i]);
//            field.setAccessible(true);
//            field.set(cyMadeSelectResult, label[i]);
//        }
//        List<CyMadeSelectResult> exportList = new ArrayList<>();
//        exportList.add(cyMadeSelectResult);
//        exportList.addAll(results);
//        ExportParams exportParams = new ExportParams(tbExcelConfig.getTitle(), tbExcelConfig.getSheetName(), ExcelType.XSSF);
//        //如果不想用easyPoi自动生成表头就用下面的这行代码
//        exportParams.setCreateHeadRows(false);
//        map.put(NormalExcelConstants.DATA_LIST,exportList);
//        map.put(NormalExcelConstants.CLASS, CyMadeSelectResult.class);
//        map.put(NormalExcelConstants.PARAMS, exportParams);
//        map.put(NormalExcelConstants.FILE_NAME, "色板管理系统" + date + tbExcelConfig.getSheetName());
//        PoiBaseView.render(map, request, response, NormalExcelConstants.EASYPOI_EXCEL_VIEW);
//    }

    /**
     * 海量数据读取
     */
    public static List<String> read(File file) throws FileNotFoundException {
        FileInputStream inputStream = new FileInputStream(file);
        Workbook workbook = StreamingReader.builder()
                .rowCacheSize(100)
                .bufferSize(4098)
                .open(inputStream);
        Sheet sheet = workbook.getSheetAt(0);
        List<String> list = new ArrayList<>();
        for (Row row : sheet) {
            // 每行的个数
            for (int i = 0; i < row.getLastCellNum(); i++) {
                String value = getValue(row.getCell(i));
                list.add(value);
            }
        }
        return list;
    }

    private static String getValue(Cell xCell) {
        DecimalFormat df = new DecimalFormat("0.00");
        if (xCell == null) {
            return "";
        }
        if (xCell.getCellType() == CellType.BOOLEAN) {
            return String.valueOf(xCell.getBooleanCellValue());
        } else if (xCell.getCellType() == CellType.NUMERIC) {
            return df.format(Double.valueOf(xCell.getNumericCellValue())) + "";
        } else if (xCell.getCellType() == CellType.STRING) {
            return xCell.getStringCellValue();
        } else {
            return "";
        }
    }

    /**
     * 充值导入
     */
    /*public static List<CyRechargeInfo> readExcelRecharge(File file) throws FileNotFoundException {
        FileInputStream inputStream = new FileInputStream(file);
        Workbook workbook = StreamingReader.builder()
                .rowCacheSize(100)
                .bufferSize(4098)
                .open(inputStream);
        Sheet sheet = workbook.getSheetAt(0);
        List<CyRechargeInfo> list = new ArrayList<>();
        for (Row row : sheet) {
            // 每行的个数
            CyRechargeInfo b = new CyRechargeInfo();
            b.setDeviceNumber(row.getCell(0).getStringCellValue());
            b.setFee(getValue(row.getCell(1)));
            list.add(b);
        }
        return list;
    }*/

    /**
     * 海量数据写入
     **/
    public static SXSSFWorkbook write(ResultSet resultSet) throws Exception {
        SXSSFWorkbook wb = new SXSSFWorkbook(-1);
        SXSSFSheet sh = wb.createSheet();
        int num = 0;
        while (resultSet.next()) {
            Row row = sh.createRow(num);
            Cell cell = row.createCell(0);
            cell.setCellValue(resultSet.getString("callee"));
            if (num % 100 == 0) {
                sh.flushRows(100);
            }
            num++;
        }
        return wb;
    }

    /**
     * 定制查询导入读取
     *
     * @return
     */
    /*public static Map<String, Object> madeRead(File file) throws FileNotFoundException, NoSuchFieldException, IllegalAccessException {
        FileInputStream inputStream = new FileInputStream(file);
        Workbook workbook = StreamingReader.builder()
                .rowCacheSize(100)
                .bufferSize(4098)
                .open(inputStream);
        Sheet sheet = workbook.getSheetAt(0);
        String batchId = new SimpleDateFormat("YYYYMMDDHHmmss").format(new Date());
        List<CyMadeSelectDetail> list = new ArrayList<>();
        for (Row row : sheet) {
            CyMadeSelectDetail cyMadeSelectDetail = new CyMadeSelectDetail();
            cyMadeSelectDetail.setSelectBatchId(batchId);
            // 每行的个数
            for (int i = 0; i < 30; i++) {
                Field field = cyMadeSelectDetail.getClass().getDeclaredField("selectRow" + (i + 1));
                field.setAccessible(true);
                field.set(cyMadeSelectDetail, formatCell(row.getCell(i)));
            }
            list.add(cyMadeSelectDetail);
        }
        list.remove(0);
        Map<String, Object> map = new HashMap<>();
        map.put(GlobalContants.DATA, list);
        map.put(GlobalContants.BATCHID, batchId);
        return map;
    }*/


    /**
     * 格式化cell
     *
     * @param cell
     * @return
     */
    private static String formatCell(Cell cell) {
        if (cell == null || cell.getCellType() == CellType.BLANK) {
            return "";
        }
        if (cell.getCellType() == CellType.BOOLEAN) {
            return String.valueOf(cell.getBooleanCellValue());
        } else if (cell.getCellType() == CellType.NUMERIC) {
            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(cell.getDateCellValue());
            }
            StreamingCell sCell = (StreamingCell) cell;
            return sCell.getStringCellValue();
        } else if (cell.getCellType() == CellType.STRING) {
            return cell.getStringCellValue();
        } else {
            return "";
        }
    }
}
